package main;

import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Scanner;
import java.util.logging.SimpleFormatter;


/**
 * Created by freewheel on 2016/10/10.
 */
public class AdministratorHelper {

    private static final int CREATE_ALL_TABLES = 1;
    private static final int DELETE_ALL_TABLES = 2;
    private static final int LOAD_FROM_FILE = 3;
    private static final int SHOW_TABLES_NUM = 4;
    private static final int RETURN_MAIN = 5;


    public static void printAdminOperationsMenu() {
        String menu = "-----Operations for administrator menu-----\n" +
                "What kinds of operation would you like to perform?\n" +
                "1. Create all tables\n" +
                "2. Delete all tables\n" +
                "3. Load from datafile\n" +
                "4. Show number of records in each table\n" +
                "5. Return to main menu\n" +
                "Enter your Choice: ";
        System.out.println(menu);
    }

    public static void performOperationsByOption(int option) {
        switch (option) {
            case CREATE_ALL_TABLES: {
                createAllTables();
                break;
            }
            case DELETE_ALL_TABLES: {
                deleteAllTables();
                break;
            }
            case LOAD_FROM_FILE: {
                deleteAllTables();
                createAllTables();
                loadFromDatafile();
                break;
            }
            case SHOW_TABLES_NUM: {
                showTableTuples();
                break;
            }
            case RETURN_MAIN: {
                Entry.enterMainMenu();
                return;
            }
            default:
                System.out.println("\nPlease Input Number 1  to 5 !");
                break;
        }
        Entry.showMenuByRole(Role.ADMINISTRATOR);
    }

    private static void createAllTables() {
        Connection conn = DBHelper.getConnection();
        Statement statement = null;
        try {
            statement = conn.createStatement();
            dropTablesIfExist(statement);

            String createManufaturersSql = "CREATE TABLE manufacturer(" +
                    "mID INTEGER NOT NULL AUTO_INCREMENT," +
                    "mName VARCHAR(20) NOT NULL, " +
                    "mAddress VARCHAR(50) NOT NULL," +
                    "mPhoneNumber  VARCHAR(8) NOT NULL," +
                    "PRIMARY KEY (mID)" +
                    ");";

            String createSalespersonsSql = "CREATE TABLE salesperson(" +
                    "sID INTEGER NOT NULL AUTO_INCREMENT," +
                    "sName VARCHAR(20) NOT NULL, " +
                    "sAddress VARCHAR(50) NOT NULL," +
                    "sPhoneNumber  VARCHAR(8) NOT NULL," +
                    "sExperience Int(1) NOT NULL, " +
                    "PRIMARY KEY (sID)" +
                    ");";

            String createCategoriesSql = "CREATE TABLE category(" +
                    "cID INTEGER NOT NULL AUTO_INCREMENT," +
                    "cName VARCHAR(20) NOT NULL, " +
                    "PRIMARY KEY (cID));";

            String createPartsSql = "CREATE TABLE part(" +
                    "pID INTEGER NOT NULL AUTO_INCREMENT," +
                    "cID INTEGER NOT NULL," +
                    "mID INTEGER NOT NULL," +
                    "pName VARCHAR(20) NOT NULL, " +
                    "pPrice Int(5) NOT NULL," +
                    "pWarrantyPeriod Int(5) NOT NULL," +
                    "pAvailableQuantity  Int(2) NOT NULL," +
                    "PRIMARY KEY (pID)," +
                    "FOREIGN KEY (cID) REFERENCES category(cID)," +
                    "FOREIGN KEY (mID) REFERENCES manufacturer(mID)" +
                    ");";

            String createTransactionRecordsSql = "CREATE TABLE transaction(" +
                    "tID INTEGER NOT NULL AUTO_INCREMENT," +
                    "pID INTEGER NOT NULL," +
                    "sID INTEGER NOT NULL," +
                    "tDate Date NOT NULL," +
                    "PRIMARY KEY (tID)," +
                    "FOREIGN KEY (pid) REFERENCES part(pID)," +
                    "FOREIGN KEY (sID) REFERENCES salesperson(sID)" +
                    ");";

            statement.execute(createManufaturersSql);
            statement.execute(createSalespersonsSql);
            statement.execute(createCategoriesSql);
            statement.execute(createPartsSql);
            statement.execute(createTransactionRecordsSql);

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    private static void dropTablesIfExist(Statement statement) throws SQLException {
        String dropSql = "DROP TABLE IF EXISTS transaction;";
        String dropSql1 = "DROP TABLE IF EXISTS part;";
        String dropSql2 = "DROP TABLE IF EXISTS category;";
        String dropSql3 = "DROP TABLE IF EXISTS salesperson;";
        String dropSql4 = "DROP TABLE IF EXISTS manufacturer;";
        statement.execute(dropSql);
        statement.execute(dropSql1);
        statement.execute(dropSql2);
        statement.execute(dropSql3);
        statement.execute(dropSql4);
    }

    private static void deleteAllTables() {
        Connection conn = DBHelper.getConnection();
        Statement statement = null;
        try {
            statement = conn.createStatement();
            dropTablesIfExist(statement);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    private static void loadFromDatafile() {
        System.out.println("Type in the Source Data Folder Path: ");
        Scanner sc = Entry.getScanner();
        if (sc.hasNextLine()) {
            String folderPath = sc.nextLine();

            Connection conn = DBHelper.getConnection();
            PreparedStatement statement = null;
            try {
                statement = conn.prepareStatement("Insert into manufacturer (mID,mName,mAddress,mPhoneNumber)Values(?,?,?,?);");

                try {
                    BufferedReader reader = new BufferedReader(new FileReader(folderPath + "/manufacturer.txt"));
                    String line;

                    while ((line = reader.readLine()) != null) {
                        String[] fields = line.split("\t");
                        int mid = Integer.parseInt(fields[0]);
                        String mName = fields[1];
                        String mAddress = fields[2];
                        String mPhonenumber = fields[3];

                        statement.setInt(1, mid);
                        statement.setString(2, mName);
                        statement.setString(3, mAddress);
                        statement.setString(4, mPhonenumber);

                        statement.execute();
                    }

                    statement = conn.prepareStatement("Insert into salesperson (sID,sName,sAddress,sPhoneNumber,sExperience)Values(?,?,?,?,?);");
                    reader = new BufferedReader(new FileReader(folderPath + "/salesperson.txt"));
                    while ((line = reader.readLine()) != null) {
                        String[] fields = line.split("\t");
                        int sid = Integer.parseInt(fields[0]);
                        String sName = fields[1];
                        String sAddress = fields[2];
                        String sPhonenumber = fields[3];
                        int experience = Integer.parseInt(fields[4]);

                        statement.setInt(1, sid);
                        statement.setString(2, sName);
                        statement.setString(3, sAddress);
                        statement.setString(4, sPhonenumber);
                        statement.setInt(5, experience);

                        statement.execute();
                    }

                    statement = conn.prepareStatement("Insert into category (cID,cName)Values(?,?);");
                    reader = new BufferedReader(new FileReader(folderPath + "/category.txt"));
                    while ((line = reader.readLine()) != null) {
                        String[] fields = line.split("\t");
                        int cid = Integer.parseInt(fields[0]);
                        String cName = fields[1];

                        statement.setInt(1, cid);
                        statement.setString(2, cName);

                        statement.execute();
                    }

                    statement = conn.prepareStatement("Insert into part (pID,pName,pPrice,mID,cID,pWarrantyPeriod,pAvailableQuantity)Values(?,?,?,?,?,?,?);");
                    reader = new BufferedReader(new FileReader(folderPath + "/part.txt"));
                    while ((line = reader.readLine()) != null) {
                        String[] fields = line.split("\t");
                        int pid = Integer.parseInt(fields[0]);
                        String pName = fields[1];
                        int pPrice = Integer.parseInt(fields[2]);
                        int mid = Integer.parseInt(fields[3]);
                        int cid = Integer.parseInt(fields[4]);
                        int warranty = Integer.parseInt(fields[5]);
                        int quantity = Integer.parseInt(fields[6]);


                        statement.setInt(1, pid);
                        statement.setString(2, pName);
                        statement.setInt(3, pPrice);
                        statement.setInt(4, mid);
                        statement.setInt(5, cid);
                        statement.setInt(6, warranty);
                        statement.setInt(7, quantity);

                        statement.execute();
                    }

                    statement = conn.prepareStatement("Insert into transaction (tID,pID,sID,tDate)Values(?,?,?,?);");
                    reader = new BufferedReader(new FileReader(folderPath + "/transaction.txt"));
                    SimpleDateFormat format = new SimpleDateFormat("dd/MM/yyyy");
                    while ((line = reader.readLine()) != null) {
                        String[] fields = line.split("\t");
                        int tid = Integer.parseInt(fields[0]);
                        int pid = Integer.parseInt(fields[1]);
                        int sid = Integer.parseInt(fields[2]);
                        Date tDate = new Date(format.parse(fields[3]).getTime());

                        statement.setInt(1, tid);
                        statement.setInt(2, pid);
                        statement.setInt(3, sid);
                        statement.setDate(4, tDate);

                        statement.execute();
                    }

                    System.out.println("Done! Data is inputted to the database!");

                } catch (FileNotFoundException e) {
                    e.printStackTrace();
                } catch (IOException e) {
                    e.printStackTrace();
                } catch (ParseException e) {
                    e.printStackTrace();
                }

            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                if (statement != null) {
                    try {
                        statement.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
    }

    public static void showTableTuples() {
        Connection conn = DBHelper.getConnection();
        Statement statement = null;
        try {
            statement = conn.createStatement();
            System.out.println("Number of records of each table:");

            List<String> tableNames = DBHelper.getTableNames();

            for (int i = 0; i < tableNames.size(); i++) {
                String tableName = tableNames.get(i);

                if (DBHelper.tableExist(tableName)) {
                    System.out.print(tableName + ": ");
                    String sql = "SELECT COUNT(*) FROM " + tableName + " ;";
                    ResultSet resultSet = statement.executeQuery(sql);
                    if (resultSet.next()) {
                        int count = resultSet.getInt(1);
                        System.out.println(count);
                    } else {
                        throw new RuntimeException("No table " + tableName + " exist!");
                    }
                }
            }
            System.out.println("End of showing number of records.");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }


}
